-- @owner: opentestcase018
-- @date: 2022/01/07
-- @testpoint: insertpt index--事务回滚

--step1:建表;expect:成功
drop table if exists t_ustore_insert_0018 cascade;
create table t_ustore_insert_0018(
c_id int,
c_d_id int,
c_w_id int,
c_first varchar(16),
c_middle char(16),
c_last varchar(16)
) with (storage_type=ustore);

select count(*) from t_ustore_insert_0018;
analyze t_ustore_insert_0018;
explain  (costs false)  select count(*) from t_ustore_insert_0018 where c_id between 10000 and 20000;

--step2:事务回滚并查询数据;expect:成功
begin;
create index index_ustore_0018 on t_ustore_insert_0018(c_id) with (indexsplit=insertpt);
insert into t_ustore_insert_0018 values (generate_series(1,140000),generate_series(1,140000),generate_series(1,140000),'abc','cde','fds');
update t_ustore_insert_0018 set c_id = 30000 where c_id between 40000 and 60000;
select count(*) from t_ustore_insert_0018 where c_id = 30000;
explain  (costs false)  select count(*) from t_ustore_insert_0018 where c_id = 30000;
delete from  t_ustore_insert_0018 where c_id > 100000;
select count(*) from t_ustore_insert_0018 where c_id >90000;
select relname from pg_class where oid in  (select indexrelid from pg_index where indrelid = (select oid from pg_class where relname ='t_ustore_insert_0018'));
rollback;
/
select count(*) from t_ustore_insert_0018;
select count(*) from t_ustore_insert_0018 where c_id between 10000 and 20000;
select relname from pg_class where oid in  (select indexrelid from pg_index where indrelid = (select oid from pg_class where relname ='t_ustore_insert_0018'));

--step3:清理环境;expect:成功
drop table if exists t_ustore_insert_0018 cascade;



